package ie.dcu.spca.model.dao;

import ie.dcu.spca.model.Bean;
import ie.dcu.spca.model.CommonOperation;
import ie.dcu.spca.model.DBconnection;
import ie.dcu.spca.model.Dao;
import ie.dcu.spca.model.bean.Product;
import ie.dcu.spca.model.sql.productSQL;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.database.Cursor;

public class ProductDao extends Dao implements CommonOperation {
    private Product cursorToProduct(Cursor cursor) {
        cursor.moveToNext();
        int idIndex = cursor.getColumnIndex("id");
        int nameIndex = cursor.getColumnIndex("name");
        int priceIndex = cursor.getColumnIndex("price");
        int barcodeIndex = cursor.getColumnIndex("barcode");
        int unit_idIndex = cursor.getColumnIndex("unit_id");
        int category_idIndex = cursor.getColumnIndex("category_id");
        int creat_atIndex = cursor.getColumnIndex("create_at");
        int update_atIndex = cursor.getColumnIndex("update_at");
        int delete_atIndex = cursor.getColumnIndex("delete_at");
        int id = cursor.getInt(idIndex);
        String name = cursor.getString(nameIndex);
        double price = cursor.getDouble(priceIndex);
        String barcode = cursor.getString(barcodeIndex);
        int unit_id = cursor.getInt(unit_idIndex);
        int category_id = cursor.getInt(category_idIndex);
        long creat_at = cursor.getLong(creat_atIndex);
        long update_at = cursor.getLong(update_atIndex);
        long delete_at = cursor.getLong(delete_atIndex);
        cursor.close();
        return new Product(id, name, price, barcode, unit_id, category_id,
                creat_at, update_at, delete_at);
    }

    private List<Product> cursorToProductList(Cursor cursor) {
        List<Product> productList = new ArrayList<Product>();
        while (cursor.moveToNext()) {
            int idIndex = cursor.getColumnIndex("id");
            int nameIndex = cursor.getColumnIndex("name");
            int priceIndex = cursor.getColumnIndex("price");
            int barcodeIndex = cursor.getColumnIndex("barcode");
            int unit_idIndex = cursor.getColumnIndex("unit_id");
            int category_idIndex = cursor.getColumnIndex("category_id");
            int create_atIndex = cursor.getColumnIndex("create_at");
            int update_atIndex = cursor.getColumnIndex("update_at");
            int delete_atIndex = cursor.getColumnIndex("delete_at");
            int id = cursor.getInt(idIndex);
            String name = cursor.getString(nameIndex);
            double price = cursor.getDouble(priceIndex);
            String barcode = cursor.getString(barcodeIndex);
            int unit_id = cursor.getInt(unit_idIndex);
            int category_id = cursor.getInt(category_idIndex);
            long creat_at = cursor.getLong(create_atIndex);
            long update_at = cursor.getLong(update_atIndex);
            long delete_at = cursor.getLong(delete_atIndex);
            productList.add(new Product(id, name, price, barcode, unit_id,
                    category_id, creat_at, update_at, delete_at));
        }
        cursor.close();
        return productList;
    }

    public List<Product> selectProductListByName(String name) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(productSQL.selectProductListByName, name);
        Cursor cursor = conn.querySql(sql);
        return this.cursorToProductList(cursor);
    }

    public List<Product> selectProductListByBarcode(double barcode) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(productSQL.selectProductListByBarcode,
                barcode);
        Cursor cursor = conn.querySql(sql);
        return this.cursorToProductList(cursor);
    }

    public List<Product> selectProductByCategoryId(int id) {
        DBconnection conn = DBconnection.getInstance();
        CategoryDao cd = new CategoryDao();
        if (!cd.existById(id))
            return null;
        String sql = String.format(productSQL.selectProductByCategoryId, id);
        Cursor cursor = conn.querySql(sql);
        return this.cursorToProductList(cursor);
    }

    public int insertProdect(String name, double price, String barcode,
            int unit_id, int category_id) {
        DBconnection conn = DBconnection.getInstance();
        int id = this.selectMaxId() + 1;
        CategoryDao cd = new CategoryDao();
        if (!cd.existById(category_id))
            return -1;
        Object[] args = {id, name, price,
                barcode, unit_id, category_id, System.currentTimeMillis(),  System.currentTimeMillis(), 0};
        conn.executeSql(productSQL.insertProdect,args);
        return id;
    }

    @Override
    public Bean selectById(int id) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(productSQL.selectById, id);
        Cursor cursor = conn.querySql(sql);
        return this.cursorToProduct(cursor);
    }

    @Override
    public int insertByBean(Bean bean) {
        DBconnection conn = DBconnection.getInstance();
        Product product = (Product) bean;
        int id = product.getId();
        if (this.existById(id))
            return -1;
        String name = product.getName();
        double price = product.getPrice();
        String barcode = product.getBarcode();
        int unit_id = product.getUnit_id();
        int category_id = product.getCategory_id();
        Object[] args = {id, name, price,
                barcode, unit_id, category_id, System.currentTimeMillis(), System.currentTimeMillis(), 0};
        conn.executeSql(productSQL.insertByBean,args);
        return id;
    }

    @Override
    public boolean deleteById(int id) {
        DBconnection conn = DBconnection.getInstance();
        if (!this.existById(id))
            return false;
        Object[] args = {(new Date())
                .getTime(), id};
        conn.executeSql(productSQL.deleteById, args);
        return true;
    }

    @Override
    public boolean deleteByBean(Bean bean) {
        return this.deleteById(((Product) bean).getId());
    }

    @Override
    public boolean updateByBean(Bean bean) {
        DBconnection conn = DBconnection.getInstance();
        Product product = (Product) bean;
        int id = product.getId();
        if (!this.existById(id))
            return false;
        String name = product.getName();
        double price = product.getPrice();
        String barcode = product.getBarcode();
        int unit_id = product.getUnit_id();
        int category_id = product.getCategory_id();
        long create_at = product.getCreate_at().getTime();
        long update_at = System.currentTimeMillis();
        long delete_at = product.getDelete_at().getTime();
        Object[] args={ name, price,
                barcode, unit_id, category_id, create_at, update_at, delete_at,
                id};
        conn.executeSql(productSQL.updateByBean,args);
        return true;
    }

    @Override
    public int selectMaxId() {
        DBconnection conn = DBconnection.getInstance();
        String sql = productSQL.selectMaxId;
        Cursor cursor = conn.querySql(sql);
        int maxid = 0;
        if (cursor.moveToNext()) {
            int idindex = cursor.getColumnIndex("max");
            maxid = cursor.getInt(idindex);
        }
        cursor.close();
        return maxid;
    }

    @Override
    public boolean existById(int id) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(productSQL.existById, id);
        Cursor cursor = conn.querySql(sql);
        boolean f = false;
        if (cursor.moveToNext())
            f = true;
        cursor.close();
        return f;
    }

    @Override
    public boolean isDeletedById(int id) {
        DBconnection conn = DBconnection.getInstance();
        String sql = String.format(productSQL.isDeletedById, id);
        Cursor cursor = conn.querySql(sql);
        boolean f = false;
        if (cursor.moveToNext())
            f = true;
        cursor.close();
        return f;
    }

    @Override
    public List<Bean> selectAllBean() {
        DBconnection conn = DBconnection.getInstance();
        String sql = productSQL.selectAllBean;
        Cursor cursor = conn.querySql(sql);
        List<Product> productlist = this.cursorToProductList(cursor);
        List<Bean> beanlist = new ArrayList<Bean>();
        for (Product b : productlist) {
            beanlist.add(b);
        }
        cursor.close();
        return beanlist;
    }

}
